package eBrain.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import org.apache.catalina.Session;

import eBrain.beans.Bid;
import eBrain.beans.User;

import eBrain.beans.Item;

public class BidPeer {
	
	public static ArrayList getBidsForItem(DataManager dataManager, int itemid) {
		Connection connection = dataManager.getConnection();
		ArrayList<Bid> bids = new ArrayList();
		PreparedStatement pstmt = null;
		if (connection != null) {
			try {
				String sql = "select USER_ID, TIMESTAMP, BID_VALUE  from  BID  WHERE ITEM_ID=? ORDER BY BID_VALUE DESC";
				pstmt = connection.prepareStatement(sql); 
				pstmt.setInt(1, itemid);
				ResultSet rs = pstmt.executeQuery();		
					try {
						while (rs.next()) {
							Bid bid = new Bid();
							bid.setUserId(rs.getInt(1));
							bid.setTimestamp(new Timestamp(rs.getTimestamp(2).getTime()));
							bid.setBidValue(rs.getDouble(3));
							bids.add(bid);
						}
					} finally {
						rs.close();
						pstmt.close();
					}
				
			} catch (SQLException e) {
				System.out.println("Could not get Bids for Item_id: "+ itemid + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}
		return bids;
	}
	
	public static boolean setBid(DataManager dataManager, Bid bid){
		
		 Connection conn = dataManager.getConnection();
		   PreparedStatement pstmt = null;
		
		try {
			  String sql = "INSERT INTO BID VALUES (?,?,?,?,null,null)";
			  pstmt = conn.prepareStatement(sql); // create a statement
		      pstmt.setInt(1, bid.getItemId());// set input parameter 1
		      pstmt.setInt(2, bid.getUserId());
			  pstmt.setDouble(3, bid.getBidValue());
			  pstmt.setTimestamp(4, bid.getTimestamp());
		      pstmt.executeUpdate(); // execute update statement
		    } catch (Exception e) {
		      e.printStackTrace();
		      System.exit(1);
		    } finally {
		      try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		      try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		    }
		return true;
	}
	
	public static Bid getMaxBidForItem(DataManager dataManager, int itemid){
		Bid bid = new Bid();
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT * FROM BID b, (SELECT max(BID_VALUE) as maxbid FROM BID Group by item_id) maxresults WHERE b.BID_VALUE= maxresults.maxbid and b.ITEM_ID=?";
				pstmt = connection.prepareStatement(sql); 
				pstmt.setInt(1, itemid);
				ResultSet rs = pstmt.executeQuery();		

				try {
					try {
						while (rs.next()) {
							bid.setItemId(rs.getInt(1));
							bid.setUserId(rs.getInt(2));
							bid.setBidValue(rs.getDouble(3));
							bid.setTimestamp(rs.getTimestamp(4));
							bid.setRatUserId(rs.getInt(5));
							bid.setRatingId(rs.getInt(6));
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get max bid for item: "+ itemid+ "  " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}

		return bid;
	}
	
	public static Bid getNextToLast(DataManager dataManager, int itemid){
		Bid bid = new Bid();
		Connection connection = dataManager.getConnection();
		PreparedStatement pstmt = null;
		
		if (connection != null) {
			try {
				Statement s = connection.createStatement();
				String sql = "SELECT * FROM BID WHERE ITEM_ID=? ORDER BY BID_VALUE DESC";
				pstmt = connection.prepareStatement(sql); 
				pstmt.setInt(1, itemid);
				ResultSet rs = pstmt.executeQuery();		

				try {
					try {
						while(rs.next()){
						if(rs.getRow()==2) {
							bid.setItemId(rs.getInt(1));
							bid.setUserId(rs.getInt(2));
							bid.setBidValue(rs.getDouble(3));
							bid.setTimestamp(rs.getTimestamp(4));
							bid.setRatUserId(rs.getInt(5));
							bid.setRatingId(rs.getInt(6));
						}
						}
					} finally {
						rs.close();
					}
				} finally {
					s.close();
				}
			} catch (SQLException e) {
				System.out.println("Could not get next to last for item: "+ itemid+ "  " + e.getMessage());
			} finally {
				dataManager.putConnection(connection);
			}
		}

		return bid;
	}
	

}
